 IF EXISTS (
	SELECT *
	FROM sysobjects
	WHERE id = object_id(N'dbo.spFdsNHumanReviewCleanup')
	AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	
	BEGIN
		
		PRINT 'Dropping spFdsNHumanReviewCleanup'
        DROP PROCEDURE dbo.spFdsNHumanReviewCleanup
        
	END

GO
	
/*******************************************************************************
* Name:					spFdsNHumanReviewCleanup
* Purpose:				Removes all reviews (including boards and details) that
*						were not brought over in the latest feed -- these
*						reviews have been orphaned for one reason or another
*						(e.g. a protocol was cloned).
*
* ERRORS USED			Description
* -------------			--------------------------------------------------------
* None
*
* RETURN VALUE
* Value    				Description					
* --------------		--------------------------------------------------------
* None
********************************************************************************/

CREATE PROCEDURE spFdsNHumanReviewCleanup
AS

BEGIN

	SET NOCOUNT ON

	DECLARE @_boardReviewDetailId	AS INTEGER
	
	DECLARE @_SUCCESS AS BIT
	DECLARE @_FAILURE AS BIT
	SET @_SUCCESS = 1
	SET @_FAILURE = 0
	
	
	-------------------------------------------------------------------------
	-- 1. Account for board review details
	-------------------------------------------------------------------------
	DECLARE cursorBoardReviewDetailsToDelete CURSOR LOCAL FAST_FORWARD FOR
		SELECT	Id
		FROM	RevBoardReviewDetail BoardReviewDetail
		WHERE  	BoardReviewDetail.Id NOT IN (
					SELECT	RevBoardReviewDetailId
					FROM	FdsProtocolStagingBoardReviewDetail
					WHERE	RevBoardReviewDetailId IS NOT NULL)
	
	BEGIN TRANSACTION spFdsNHumanReviewCleanup

	OPEN cursorBoardReviewDetailsToDelete
	FETCH NEXT FROM cursorBoardReviewDetailsToDelete INTO @_boardReviewDetailId
	WHILE @@FETCH_STATUS = 0
  	BEGIN
  	
  		-- delete the rex board review data
  		DELETE FROM	RevRexBoardReviewDetail
		WHERE  RevBoardReviewDetailId = @_boardReviewDetailId
		
		IF @@ERROR > 0 GOTO ERROR_HANDLER
			
		-- delete the review notification data
		DELETE  FROM RevReviewNotification
		WHERE  	RevBoardReviewDetailId = @_boardReviewDetailId

		IF @@ERROR > 0 GOTO ERROR_HANDLER
			
		-- delete the review meeting data
		DELETE  FROM RevReviewMeeting
		WHERE  	RevBoardReviewDetailId = @_boardReviewDetailId
		
		IF @@ERROR > 0 GOTO ERROR_HANDLER
		
		-- delete the board review detail
		DELETE  FROM RevBoardReviewDetail
		WHERE  	Id = @_boardReviewDetailId
		
		IF @@ERROR > 0 GOTO ERROR_HANDLER
		
		FETCH NEXT FROM cursorBoardReviewDetailsToDelete INTO @_boardReviewDetailId
  	
  	END
  	
  	-- close and deallocate cursor
	CLOSE cursorBoardReviewDetailsToDelete
  	DEALLOCATE cursorBoardReviewDetailsToDelete
  	
   	-------------------------------------------------------------------------
	-- 2. Account for board reviews
	-------------------------------------------------------------------------
	
	-- delete board review data
	DELETE	FROM RevRexBoardReview
	WHERE  	RevBoardReviewId NOT IN (
				SELECT	RevBoardReviewId
				FROM	FdsProtocolStagingBoardReview
				WHERE	RevBoardReviewId IS NOT NULL	)
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER
	
	DELETE	FROM RevBoardReview
	WHERE  	Id NOT IN (
				SELECT	RevBoardReviewId
				FROM	FdsProtocolStagingBoardReview
				WHERE	RevBoardReviewId IS NOT NULL	)
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER
	
	
    -------------------------------------------------------------------------
	-- 3. Account for reviews themselves
	-------------------------------------------------------------------------

	DELETE	FROM RevReview
	WHERE  	Id NOT IN (
				SELECT	RevReviewId
				FROM	FdsProtocolStagingProtocolReview
				WHERE	RevReviewId IS NOT NULL	)

	IF @@ERROR > 0 GOTO ERROR_HANDLER
	
	SET NOCOUNT OFF
	 
	COMMIT TRANSACTION spFdsNHumanReviewCleanup
	RETURN @_SUCCESS

ERROR_HANDLER:
	BEGIN
		ROLLBACK TRANSACTION spFdsNHumanReviewCleanup
		RETURN @_FAILURE
	END
END